﻿/******************************************
 * CopyRight:   T2M
 * FileName:    ExcelHelper.cs
 * Author:      Liuxp
 * CreateDate:  2015-05-12 16:00:10
 * Description: Excel 文件操作
 * History:     
 ******************************************/
using System;
using System.IO;
using System.Data;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections.Generic;
using System.Reflection;

namespace ARchGL.Declaration.Platform.Web.Common
{
    public static class ExcelHelper
    {
        /// <summary>
        /// 将 Excel 转换为 DataTable
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetIndex">表下标 从0开始</param>
        /// <param name="header"> 是否排除excel中第一行</param>
        /// <returns>DataTable</returns>
        public static DataTable ExcelToDataTable(String filePath, Int32 sheetIndex, int rowIndex, Boolean header = true)
        {
            var ext = CheckExt(filePath);

            if (ext == "208207")//xls
                return ToDataTable2003(filePath, sheetIndex, rowIndex, header);

            if (ext == "8075")//xlsx
                return ToDataTable2007(filePath, sheetIndex, header);

            return new DataTable();
        }

        #region Private Method

        /// <summary>
        /// 通过文件头信息判断文件类型
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private static string CheckExt(string path)
        {
            var fs = new FileStream(path, FileMode.Open, FileAccess.Read);
            var r = new BinaryReader(fs);

            var bx = string.Empty;

            try
            {
                byte buffer = r.ReadByte();
                bx = buffer.ToString();
                buffer = r.ReadByte();
                bx += buffer.ToString();

                // 208207 xls
                // 8075 xlsx
            }
            catch (Exception)
            {
                //throw;
            }
            finally
            {
                r.Close();
                fs.Close();
            }
            return bx;
        }

        /// <summary>
        /// 将 2003 Excel 转换为 DataTable
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetIndex">表下标 从0开始</param>
        /// <param name="rowIndex">行下标 从1开始</param>
        /// <param name="header"> excel中第一行是否属于列</param>
        /// <returns>DataTable</returns>
        private static DataTable ToDataTable2003(String filePath, Int32 sheetIndex, int rowIndex, Boolean header)
        {
            var dt = new DataTable(Path.GetFileNameWithoutExtension(filePath) + "_Sheet" + sheetIndex);

            using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                var workbook = new HSSFWorkbook(file);
                var sheet = workbook.GetSheetAt(sheetIndex);

                sheet.SetColumnHidden(0, false);
                sheet.SetColumnHidden(1, false);
                sheet.SetColumnHidden(2, false);

                var rows = sheet.GetRowEnumerator();
                for (int i = 0; i < rowIndex; i++)
                {
                    rows.MoveNext();
                }

                var row = (HSSFRow)rows.Current;

                for (var i = 0; i < row.LastCellNum; i++)
                {
                    var columnName = header ? row.GetCell(i).StringCellValue : i.ToString();
                    dt.Columns.Add(columnName, typeof(string));
                }

                if (!header)
                {
                    var dataRow = dt.NewRow();
                    for (var i = 0; i < row.LastCellNum; i++)
                    {
                        var item = row.GetCell(i);

                        dataRow[i] = GetRow(item);
                    }
                    dt.Rows.Add(dataRow);
                }

                while (rows.MoveNext())
                {
                    row = (HSSFRow)rows.Current;
                    var dataRow = dt.NewRow();
                    bool isNull = false;//是否空行
                    for (var i = 0; i < row.LastCellNum; i++)
                    {
                        var item = row.GetCell(i);
                        var _row = GetRow(item);
                        if (_row != null)
                        {
                            dataRow[i] = _row;
                            if (!string.IsNullOrWhiteSpace(dataRow[i].ToString()))
                            {
                                isNull = true;//设置非空
                            }
                        }
                    }
                    if (isNull)
                    {
                        dt.Rows.Add(dataRow);
                    }
                }
            }

            return dt;
        }

        /// <summary>
        /// 将 2007 Excel 转换为 DataTable
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="index">表下标 从0开始</param>
        /// <param name="header"> excel中第一行是否属于列</param>
        /// <returns>DataTable</returns>
        private static DataTable ToDataTable2007(String filePath, Int32 index, Boolean header)
        {
            var dt = new DataTable(Path.GetFileNameWithoutExtension(filePath) + "_Sheet" + index);

            using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                var workbook = new XSSFWorkbook(file);
                var sheet = workbook.GetSheetAt(index);

                var rows = sheet.GetRowEnumerator();
                rows.MoveNext();
                var row = (XSSFRow)rows.Current;

                for (var i = 0; i < row.LastCellNum; i++)
                {
                    var columnName = header ? row.GetCell(i).StringCellValue : i.ToString();
                    dt.Columns.Add(columnName, typeof(string));
                }

                if (!header)
                {
                    var dataRow = dt.NewRow();
                    for (var i = 0; i < row.LastCellNum; i++)
                    {
                        var item = row.GetCell(i);

                        dataRow[i] = GetRow(item);
                    }
                    dt.Rows.Add(dataRow);
                }

                while (rows.MoveNext())
                {
                    row = (XSSFRow)rows.Current;
                    var dataRow = dt.NewRow();
                    bool isNull = false;//是否空行
                    for (var i = 0; i < row.LastCellNum; i++)
                    {
                        var item = row.GetCell(i);
                        var _row = GetRow(item);
                        if (_row != null)
                        {
                            dataRow[i] = _row;
                            if (!string.IsNullOrWhiteSpace(dataRow[i].ToString()))
                            {
                                isNull = true;//设置非空
                            }
                        }
                    }
                    if (isNull)
                    {
                        dt.Rows.Add(dataRow);
                    }
                }
            }

            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetRow(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }

        #endregion
    }

    public class Utility
    {
        /// <summary>
        /// 将List集合转换为DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataTable ListToDataTable<T>(IEnumerable<T> list) where T : new()
        {
            if (null == list) throw new ArgumentNullException("list");
            var plist = new List<PropertyInfo>();
            Type type = typeof(T);
            DataTable dt = new DataTable();
            //将所有的public属性加入到集合并添加到DataTable的列
            Array.ForEach<PropertyInfo>(type.GetProperties(), p =>
            {
                try
                {
                    if (p.Name != "P" && p.Name != "Propertys")
                    {
                        plist.Add(p);
                        var colType = p.PropertyType;
                        if (colType.IsGenericType && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }

                        dt.Columns.Add(p.Name, colType);
                    }
                }
                catch (Exception)
                {

                    throw;
                }
            });

            foreach (var entity in list)
            {
                //创建一个dataRow实例
                DataRow row = dt.NewRow();

                //给row赋值
                plist.ForEach(p => row[p.Name] = CheckValue(p, p.GetValue(entity, null)));
                dt.Rows.Add(row);
            }
            return dt;
        }

        /// <summary>
        /// 检查空属性并赋初值
        /// </summary>
        /// <param name="info">属性信息</param>
        /// <param name="value">属性值</param>
        /// <returns></returns>
        private static object CheckValue(PropertyInfo info, object value)
        {
            var type = info.PropertyType.Name;
            DateTime miniDateTime = DateTime.Parse("1970-01-01");

            if ((info.PropertyType.BaseType == typeof(Enum) ||
                type == "Int16" || type == "Int32" || type == "Int64" || type == "Decimal") &&
                value == null)
            {
                value = 0;
            }

            if (type == "String" && value == null)
            {
                value = string.Empty;
            }

            if (type == "DateTime" && value.Equals(null))
            {
                var date = miniDateTime;
                DateTime.TryParse(value.ToString(), out date);

                if (date < miniDateTime)
                    value = miniDateTime;
            }

            return value;
        }
    }
}
